CREATE DATABASE AptitudeTest;
GO
USE AptitudeTest;
GO

-- TABLE STORGE INFO MANAGER
CREATE TABLE Manager
(
	ID			INT PRIMARY KEY IDENTITY,
	Username	VARCHAR(30) UNIQUE NOT NULL,
	[Password]	VARCHAR(50) NOT NULL,
	Email		VARCHAR(50)	NOT NULL,
	FullName	VARCHAR(50)	NOT NULL,
	Mobile		VARCHAR(15),
	[Address]	VARCHAR(100)
);

-- TABLE CATEGORY STORGE CATEGORY OF QUESTION
CREATE TABLE Category
(
	ID				INT PRIMARY KEY IDENTITY,
	CategoryName	VARCHAR(50) UNIQUE NOT NULL,
	[Description]	TEXT
);

-- TABLE QUESTION
CREATE TABLE Question
(
	ID			INT PRIMARY KEY IDENTITY,
	Content		TEXT NOT NULL,
	CategoryID	INT NOT NULL,
	[Type]		BIT	NOT NULL DEFAULT 1,
	Mark		FLOAT NOT NULL
);

-- TABLE ANSWER
CREATE TABLE Answer
(
	ID			INT PRIMARY KEY IDENTITY,
	QuestionID	INT NOT NULL,
	Content		VARCHAR(100) NOT NULL,
	IsTrue		BIT DEFAULT 2
);

-- TABLE CANDIDATE STORGE INFOMATION JOB SEEKER
CREATE TABLE Candidate
(
	ID				INT PRIMARY KEY IDENTITY,
	Username		VARCHAR(30) UNIQUE NOT NULL,
	[Password]		VARCHAR(50) NOT NULL,
	Email			VARCHAR(50) UNIQUE NOT NULL,
	[Certificate]	TEXT,
	Experience		TEXT,
	FullName		VARCHAR(50) NOT NULL,
	Mobile			VARCHAR(15),
	[Address]		VARCHAR(200)
);

-- TABLE CANDIDATEQUESTIONANSWER
CREATE TABLE CandidateQuestionAnswer
(
	ID				INT PRIMARY KEY IDENTITY,
	CandidateID		INT NOT NULL,
	ExamQuestionID	INT NOT NULL,
	Answer			VARCHAR(100) NOT NULL
);

-- TABLE EXAM
CREATE TABLE Exam
(
	ID				INT PRIMARY KEY IDENTITY,
	RestrictTime	INT NOT NULL,
	TotalMark		INT NOT NULL
);

-- TABLE EXAMQUESTION
CREATE TABLE ExamQuestion
(
	ID				INT PRIMARY KEY IDENTITY,
	QuestionID		INT NOT NULL,
	ExamID			INT NOT NULL
);

-- TABLE RESULT
CREATE TABLE Result
(
	ID				INT PRIMARY KEY IDENTITY,
	CandidateID		INT NOT NULL,
	ExamID			INT NOT NULL,
	StartTime		DATETIME NOT NULL,
	EndTime			DATETIME NOT NULL,
	Mark			FLOAT NOT NULL,
	[Status]		BIT NOT NULL
);

-- TABLE CONFIG SYSTEM
CREATE TABLE Config
(
	ID 			INT PRIMARY KEY IDENTITY,
	PageSize	INT NOT NULL DEFAULT 5,
	PageName	VARCHAR(250) NOT NULL
);

-- ADD CONSTRAINT TO TABLES
ALTER TABLE Question ADD CONSTRAINT FK_CATEGORYID_QUESTION_CATEGORY FOREIGN KEY(CategoryID) REFERENCES Category(ID);
ALTER TABLE Answer ADD CONSTRAINT FK_QUESTIONID_ANSWER_QUESTION FOREIGN KEY(QuestionID) REFERENCES Question(ID);

ALTER TABLE ExamQuestion ADD CONSTRAINT FK_QUESTIONID_EXAMQUESTION_QUESTION FOREIGN KEY(QuestionID) REFERENCES Question(ID);
ALTER TABLE ExamQuestion ADD CONSTRAINT FK_EXAMID_EXAMQUESTION_EXAM FOREIGN KEY(ExamID) REFERENCES Exam(ID);

ALTER TABLE Result ADD CONSTRAINT FK_EXAMID_RESULT_EXAM FOREIGN KEY(ExamID) REFERENCES Exam(ID);
ALTER TABLE Result ADD CONSTRAINT FK_CANDIDATEID_RESULT_CANDIDATE FOREIGN KEY(CandidateID) REFERENCES Candidate(ID);

ALTER TABLE CandidateQuestionAnswer ADD CONSTRAINT FK_EXAMQUESTIONID_THIS_EXAMQUESTION FOREIGN KEY(ExamQuestionID) REFERENCES ExamQuestion(ID);
ALTER TABLE CandidateQuestionAnswer ADD CONSTRAINT FK_CANDIDATEID_THIS_CANDIDATE FOREIGN KEY(CandidateID) REFERENCES Candidate(ID);


-- CREATE PROC
CREATE PROC DisplayCandidate
@date DATETIME
AS
SELECT a.Username,a.FullName,a.Email,a.Mobile,a.Address,b.StartTime,b.EndTime,b.Mark,b.Status FROM Candidate AS a INNER JOIN Result AS b ON a.ID=b.CandidateID WHERE b.StartTime=@date

-- ALTER PROC --
ALTER Proc [dbo].[DisplayCandidate]
@date datetime
AS
Select a.Username,a.FullName,a.Email,a.Mobile,b.StartTime,datediff(minute,b.EndTime,b.StartTime) as TestTime,b.Mark,b.Status From Candidate as a Inner Join Result as b ON a.ID=b.CandidateID WHERE b.StartTime=@date Order by b.Mark DESC

